<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>PostgreSQL 相关总结 | 寒冷如铁</title>
    <meta name="generator" content="VuePress 1.8.2">
    <link rel="icon" href="/blog/favicon.ico">
    <script language="javascript" type="text/javascript" src="https://cdn.bootcdn.net/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
    <script language="javascript" type="text/javascript" src="/blog/js/MouseClickEffect.js"></script>
    <meta name="description" content="好奇就尝试，喜欢就坚持！">
    <meta name="viewport" content="width=device-width,initial-scale=1,user-scalable=no">
    
    <link rel="preload" href="/blog/assets/css/0.styles.01ca31a9.css" as="style"><link rel="preload" href="/blog/assets/js/app.0c46c7d8.js" as="script"><link rel="preload" href="/blog/assets/js/3.5679fe00.js" as="script"><link rel="preload" href="/blog/assets/js/1.8cdd2163.js" as="script"><link rel="preload" href="/blog/assets/js/18.0a7a10ef.js" as="script"><link rel="prefetch" href="/blog/assets/js/10.0b6ffd8d.js"><link rel="prefetch" href="/blog/assets/js/11.d27e4c34.js"><link rel="prefetch" href="/blog/assets/js/12.ba5bd991.js"><link rel="prefetch" href="/blog/assets/js/13.ab5bead7.js"><link rel="prefetch" href="/blog/assets/js/14.124a0dc3.js"><link rel="prefetch" href="/blog/assets/js/15.ac521310.js"><link rel="prefetch" href="/blog/assets/js/16.b32290db.js"><link rel="prefetch" href="/blog/assets/js/17.d65c6ccd.js"><link rel="prefetch" href="/blog/assets/js/19.e0e5e91a.js"><link rel="prefetch" href="/blog/assets/js/20.bcc0e382.js"><link rel="prefetch" href="/blog/assets/js/21.59edea58.js"><link rel="prefetch" href="/blog/assets/js/22.8239cecf.js"><link rel="prefetch" href="/blog/assets/js/23.48516c82.js"><link rel="prefetch" href="/blog/assets/js/24.4e1a046f.js"><link rel="prefetch" href="/blog/assets/js/25.56c0a228.js"><link rel="prefetch" href="/blog/assets/js/26.fb764e02.js"><link rel="prefetch" href="/blog/assets/js/27.f96c7435.js"><link rel="prefetch" href="/blog/assets/js/28.31139fa0.js"><link rel="prefetch" href="/blog/assets/js/29.fa31f05f.js"><link rel="prefetch" href="/blog/assets/js/30.49c791b1.js"><link rel="prefetch" href="/blog/assets/js/31.68660a8e.js"><link rel="prefetch" href="/blog/assets/js/32.4e07e3b0.js"><link rel="prefetch" href="/blog/assets/js/33.e08324f8.js"><link rel="prefetch" href="/blog/assets/js/34.9153a266.js"><link rel="prefetch" href="/blog/assets/js/35.49d3bb37.js"><link rel="prefetch" href="/blog/assets/js/36.5a46a47a.js"><link rel="prefetch" href="/blog/assets/js/37.2403de70.js"><link rel="prefetch" href="/blog/assets/js/38.794a3bde.js"><link rel="prefetch" href="/blog/assets/js/39.185570eb.js"><link rel="prefetch" href="/blog/assets/js/4.a5921925.js"><link rel="prefetch" href="/blog/assets/js/40.b5ddc5b2.js"><link rel="prefetch" href="/blog/assets/js/41.81f131ab.js"><link rel="prefetch" href="/blog/assets/js/42.474cb45b.js"><link rel="prefetch" href="/blog/assets/js/43.fe269b94.js"><link rel="prefetch" href="/blog/assets/js/44.123909bf.js"><link rel="prefetch" href="/blog/assets/js/45.85e17df3.js"><link rel="prefetch" href="/blog/assets/js/46.96eb5367.js"><link rel="prefetch" href="/blog/assets/js/47.2c06ceb7.js"><link rel="prefetch" href="/blog/assets/js/48.dcaa5d75.js"><link rel="prefetch" href="/blog/assets/js/49.47475370.js"><link rel="prefetch" href="/blog/assets/js/5.a6bfa893.js"><link rel="prefetch" href="/blog/assets/js/50.29aa41a9.js"><link rel="prefetch" href="/blog/assets/js/51.b049cb19.js"><link rel="prefetch" href="/blog/assets/js/52.9f16a8d1.js"><link rel="prefetch" href="/blog/assets/js/53.9ccedc0d.js"><link rel="prefetch" href="/blog/assets/js/54.c16fde39.js"><link rel="prefetch" href="/blog/assets/js/55.3d2e78cc.js"><link rel="prefetch" href="/blog/assets/js/56.14b1dddb.js"><link rel="prefetch" href="/blog/assets/js/57.a48eb0da.js"><link rel="prefetch" href="/blog/assets/js/58.db985774.js"><link rel="prefetch" href="/blog/assets/js/59.dbb67461.js"><link rel="prefetch" href="/blog/assets/js/6.d5f7873e.js"><link rel="prefetch" href="/blog/assets/js/60.fd9edd5c.js"><link rel="prefetch" href="/blog/assets/js/61.c87eaa37.js"><link rel="prefetch" href="/blog/assets/js/62.09bd4303.js"><link rel="prefetch" href="/blog/assets/js/63.c3a4cb82.js"><link rel="prefetch" href="/blog/assets/js/64.1e623e6b.js"><link rel="prefetch" href="/blog/assets/js/65.5ee98079.js"><link rel="prefetch" href="/blog/assets/js/66.875fdeb3.js"><link rel="prefetch" href="/blog/assets/js/67.52e4feee.js"><link rel="prefetch" href="/blog/assets/js/68.09bb6522.js"><link rel="prefetch" href="/blog/assets/js/69.e5cc1032.js"><link rel="prefetch" href="/blog/assets/js/7.63c57787.js"><link rel="prefetch" href="/blog/assets/js/70.a2307508.js"><link rel="prefetch" href="/blog/assets/js/71.75bc515a.js"><link rel="prefetch" href="/blog/assets/js/72.f7d10a87.js"><link rel="prefetch" href="/blog/assets/js/73.ae777bb5.js"><link rel="prefetch" href="/blog/assets/js/74.ddd90b9a.js"><link rel="prefetch" href="/blog/assets/js/75.5d297adc.js"><link rel="prefetch" href="/blog/assets/js/76.299aaa21.js"><link rel="prefetch" href="/blog/assets/js/77.c93068f7.js"><link rel="prefetch" href="/blog/assets/js/78.727916c9.js"><link rel="prefetch" href="/blog/assets/js/79.605f2aff.js"><link rel="prefetch" href="/blog/assets/js/8.22284503.js"><link rel="prefetch" href="/blog/assets/js/80.0081929d.js"><link rel="prefetch" href="/blog/assets/js/9.8e870e00.js">
    <link rel="stylesheet" href="/blog/assets/css/0.styles.01ca31a9.css">
  </head>
  <body>
    <div id="app" data-server-rendered="true"><div class="theme-container" data-v-19557b78><div data-v-19557b78><div id="loader-wrapper" class="loading-wrapper" data-v-d48f4d20 data-v-19557b78 data-v-19557b78><div class="loader-main" data-v-d48f4d20><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div><div data-v-d48f4d20></div></div> <!----> <!----></div> <div class="password-shadow password-wrapper-out" style="display:none;" data-v-64685f0e data-v-19557b78 data-v-19557b78><h3 class="title" style="display:none;" data-v-64685f0e data-v-64685f0e>寒冷如铁</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-64685f0e data-v-64685f0e><input type="password" value="" data-v-64685f0e> <span data-v-64685f0e>Konck! Knock!</span> <button data-v-64685f0e>OK</button></label> <div class="footer" style="display:none;" data-v-64685f0e data-v-64685f0e><span data-v-64685f0e><i class="iconfont reco-theme" data-v-64685f0e></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-64685f0e>vuePress-theme-reco</a></span> <span data-v-64685f0e><i class="iconfont reco-copyright" data-v-64685f0e></i> <a data-v-64685f0e><span data-v-64685f0e>寒铁</span>
            
          <span data-v-64685f0e>2020 - </span>
          2022
        </a></span></div></div> <div class="hide" data-v-19557b78><header class="navbar" data-v-19557b78><div class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/blog/" class="home-link router-link-active"><!----> <span class="site-name">寒冷如铁</span></a> <div class="links"><div class="color-picker"><a class="color-button"><i class="iconfont reco-color"></i></a> <div class="color-picker-menu" style="display:none;"><div class="mode-options"><h4 class="title">Choose mode</h4> <ul class="color-mode-options"><li class="dark">dark</li><li class="auto active">auto</li><li class="light">light</li></ul></div></div></div> <div class="search-box"><i class="iconfont reco-search"></i> <input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/blog/" class="nav-link"><i class="iconfont reco-home"></i>
  首页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      分类
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/blog/categories/build/" class="nav-link"><i class="iconfont undefined"></i>
  build
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/db/" class="nav-link"><i class="iconfont undefined"></i>
  db
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/docker/" class="nav-link"><i class="iconfont undefined"></i>
  docker
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/workflow/" class="nav-link"><i class="iconfont undefined"></i>
  workflow
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/git/" class="nav-link"><i class="iconfont undefined"></i>
  git
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/java/" class="nav-link"><i class="iconfont undefined"></i>
  java
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/source/" class="nav-link"><i class="iconfont undefined"></i>
  source
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/linux/" class="nav-link"><i class="iconfont undefined"></i>
  linux
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/network/" class="nav-link"><i class="iconfont undefined"></i>
  network
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/python/" class="nav-link"><i class="iconfont undefined"></i>
  python
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/project/" class="nav-link"><i class="iconfont undefined"></i>
  project
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/share/" class="nav-link"><i class="iconfont undefined"></i>
  share
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/software/" class="nav-link"><i class="iconfont undefined"></i>
  software
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/spring/" class="nav-link"><i class="iconfont undefined"></i>
  spring
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/tomcat/" class="nav-link"><i class="iconfont undefined"></i>
  tomcat
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/web/" class="nav-link"><i class="iconfont undefined"></i>
  web
</a></li></ul></div></div><div class="nav-item"><a href="/blog/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  标签
</a></div><div class="nav-item"><a href="/blog/views/guide.html" class="nav-link"><i class="iconfont reco-other"></i>
  记录
</a></div><div class="nav-item"><a href="/blog/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  时间线
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-message"></i>
      外链
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="https://github.com/huhuhan" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-github"></i>
  GitHub
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://gitee.com/huhu_han" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  Gitee
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://console.leancloud.app/#/apps" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  有道云笔记
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://console.leancloud.app/#/apps" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  LeadCloud
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://cloud.seafile.com/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  Seafile
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://huhu_han.gitee.io/downgit" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  DownGit
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></div></div> <!----></nav></div></header> <div class="sidebar-mask" data-v-19557b78></div> <aside class="sidebar" data-v-19557b78><div class="personal-info-wrapper" data-v-b038cec6><img src="/blog/avatar.png" alt="author-avatar" class="personal-img" data-v-b038cec6> <h3 class="name" data-v-b038cec6>
    寒铁
  </h3> <div class="num" data-v-b038cec6><div data-v-b038cec6><h3 data-v-b038cec6>66</h3> <h6 data-v-b038cec6>文章</h6></div> <div data-v-b038cec6><h3 data-v-b038cec6>17</h3> <h6 data-v-b038cec6>标签</h6></div></div> <hr data-v-b038cec6></div> <nav class="nav-links"><div class="nav-item"><a href="/blog/" class="nav-link"><i class="iconfont reco-home"></i>
  首页
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-category"></i>
      分类
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/blog/categories/build/" class="nav-link"><i class="iconfont undefined"></i>
  build
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/db/" class="nav-link"><i class="iconfont undefined"></i>
  db
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/docker/" class="nav-link"><i class="iconfont undefined"></i>
  docker
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/workflow/" class="nav-link"><i class="iconfont undefined"></i>
  workflow
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/git/" class="nav-link"><i class="iconfont undefined"></i>
  git
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/java/" class="nav-link"><i class="iconfont undefined"></i>
  java
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/source/" class="nav-link"><i class="iconfont undefined"></i>
  source
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/linux/" class="nav-link"><i class="iconfont undefined"></i>
  linux
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/network/" class="nav-link"><i class="iconfont undefined"></i>
  network
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/python/" class="nav-link"><i class="iconfont undefined"></i>
  python
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/project/" class="nav-link"><i class="iconfont undefined"></i>
  project
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/share/" class="nav-link"><i class="iconfont undefined"></i>
  share
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/software/" class="nav-link"><i class="iconfont undefined"></i>
  software
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/spring/" class="nav-link"><i class="iconfont undefined"></i>
  spring
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/tomcat/" class="nav-link"><i class="iconfont undefined"></i>
  tomcat
</a></li><li class="dropdown-item"><!----> <a href="/blog/categories/web/" class="nav-link"><i class="iconfont undefined"></i>
  web
</a></li></ul></div></div><div class="nav-item"><a href="/blog/tag/" class="nav-link"><i class="iconfont reco-tag"></i>
  标签
</a></div><div class="nav-item"><a href="/blog/views/guide.html" class="nav-link"><i class="iconfont reco-other"></i>
  记录
</a></div><div class="nav-item"><a href="/blog/timeline/" class="nav-link"><i class="iconfont reco-date"></i>
  时间线
</a></div><div class="nav-item"><div class="dropdown-wrapper"><a class="dropdown-title"><span class="title"><i class="iconfont reco-message"></i>
      外链
    </span> <span class="arrow right"></span></a> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="https://github.com/huhuhan" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-github"></i>
  GitHub
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://gitee.com/huhu_han" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  Gitee
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://console.leancloud.app/#/apps" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  有道云笔记
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://console.leancloud.app/#/apps" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  LeadCloud
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://cloud.seafile.com/" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  Seafile
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li><li class="dropdown-item"><!----> <a href="https://huhu_han.gitee.io/downgit" target="_blank" rel="noopener noreferrer" class="nav-link external"><i class="iconfont reco-other"></i>
  DownGit
  <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li></ul></div></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group depth-0"><p class="sidebar-heading open"><span>PostgreSQL 相关总结</span> <!----></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/blog/views/db/postgresql.html#简介" class="sidebar-link">简介</a><ul class="sidebar-sub-headers"></ul></li><li><a href="/blog/views/db/postgresql.html#文档说明" class="sidebar-link">文档说明</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#系统表" class="sidebar-link">系统表</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#数据类型" class="sidebar-link">数据类型</a></li></ul></li><li><a href="/blog/views/db/postgresql.html#疑难随笔" class="sidebar-link">疑难随笔</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#最大连接数" class="sidebar-link">最大连接数</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#ip连接权限" class="sidebar-link">IP连接权限</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#删除连接" class="sidebar-link">删除连接</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#启动失败" class="sidebar-link">启动失败</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#链接参数" class="sidebar-link">链接参数</a></li></ul></li><li><a href="/blog/views/db/postgresql.html#sql" class="sidebar-link">SQL</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#查询系统配置" class="sidebar-link">查询系统配置</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#查询数据库表" class="sidebar-link">查询数据库表</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#查询表字段" class="sidebar-link">查询表字段</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#按时间统计" class="sidebar-link">按时间统计</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#建表语句" class="sidebar-link">建表语句</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#函数用法" class="sidebar-link">函数用法</a></li></ul></li><li><a href="/blog/views/db/postgresql.html#内部命令" class="sidebar-link">内部命令</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#登录测试" class="sidebar-link">登录测试</a></li><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#备份还原" class="sidebar-link">备份还原</a></li></ul></li><li><a href="/blog/views/db/postgresql.html#安装" class="sidebar-link">安装</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header"><a href="/blog/views/db/postgresql.html#docker-compose" class="sidebar-link">docker-compose</a></li></ul></li></ul></section></li></ul> </aside> <div class="password-shadow password-wrapper-in" style="display:none;" data-v-64685f0e data-v-19557b78><h3 class="title" style="display:none;" data-v-64685f0e data-v-64685f0e>PostgreSQL 相关总结</h3> <!----> <label id="box" class="inputBox" style="display:none;" data-v-64685f0e data-v-64685f0e><input type="password" value="" data-v-64685f0e> <span data-v-64685f0e>Konck! Knock!</span> <button data-v-64685f0e>OK</button></label> <div class="footer" style="display:none;" data-v-64685f0e data-v-64685f0e><span data-v-64685f0e><i class="iconfont reco-theme" data-v-64685f0e></i> <a target="blank" href="https://vuepress-theme-reco.recoluan.com" data-v-64685f0e>vuePress-theme-reco</a></span> <span data-v-64685f0e><i class="iconfont reco-copyright" data-v-64685f0e></i> <a data-v-64685f0e><span data-v-64685f0e>寒铁</span>
            
          <span data-v-64685f0e>2020 - </span>
          2022
        </a></span></div></div> <div data-v-19557b78><main class="page"><div class="page-title" style="display:none;"><h1>PostgreSQL 相关总结</h1> <hr> <div data-v-484a899e><i class="iconfont reco-account" data-v-484a899e><span data-v-484a899e>寒铁</span></i> <i class="iconfont reco-date" data-v-484a899e><span data-v-484a899e>2020-03-04</span></i> <i class="iconfont reco-eye" data-v-484a899e><span id="/blog/views/db/postgresql.html" data-flag-title="Your Article Title" class="leancloud-visitors" data-v-484a899e><a class="leancloud-visitors-count" style="font-size:.9rem;font-weight:normal;color:#999;"></a></span></i> <i class="iconfont reco-tag tags" data-v-484a899e><span class="tag-item" data-v-484a899e>
      db
    </span></i></div></div> <div class="theme-reco-content content__default" style="display:none;"><h2 id="简介"><a href="#简介" class="header-anchor">#</a> 简介</h2> <ul><li><p><a href="http://www.postgres.cn/v2/home" target="_blank" rel="noopener noreferrer">中文社区<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p></li> <li><p><a href="http://www.postgres.cn/v2/about" target="_blank" rel="noopener noreferrer">简介<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>：开源、事务安全性（关系型）</p></li> <li><p><a href="https://www.postgresql.org/" target="_blank" rel="noopener noreferrer">官网<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p></li></ul> <h2 id="文档说明"><a href="#文档说明" class="header-anchor">#</a> 文档说明</h2> <blockquote><p>以<strong>11版本</strong>文档为例</p></blockquote> <h3 id="系统表"><a href="#系统表" class="header-anchor">#</a> 系统表</h3> <ul><li><a href="http://www.postgres.cn/docs/11/catalog-pg-class.html" target="_blank" rel="noopener noreferrer">pg_class<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>：表、视图、索引、组合类型</li> <li><a href="http://www.postgres.cn/docs/11/catalog-pg-attribute.html" target="_blank" rel="noopener noreferrer">pg_attribute<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>：表字段</li> <li><a href="http://www.postgres.cn/docs/11/catalog-pg-type.html" target="_blank" rel="noopener noreferrer">ps_type<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>：数据类型</li></ul> <h3 id="数据类型"><a href="#数据类型" class="header-anchor">#</a> 数据类型</h3> <ul><li><a href="http://www.postgres.cn/docs/11/datatype.html" target="_blank" rel="noopener noreferrer">文档<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></li> <li>用Navicat设计表，显示的类型是<strong>别名</strong>，注意对比</li> <li>Mybatis generater里根据<strong>IColumnType</strong>生成的字段是<strong>原名字</strong> <ul><li>比如浮点类型，real=float4，double=float8，<strong>mybatis-plus默认不支持real类型</strong></li></ul></li> <li>Integer类型，对于的是<code>int4</code>，Long类型对应<code>int8</code></li></ul> <h2 id="疑难随笔"><a href="#疑难随笔" class="header-anchor">#</a> 疑难随笔</h2> <h3 id="最大连接数"><a href="#最大连接数" class="header-anchor">#</a> 最大连接数</h3> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 查看编辑配置文件，找到max_connections = 100，修改</span>
<span class="token function">vi</span> /var/lib/pgsql/9.6/data/postgresql.conf
<span class="token comment"># 重启数据库</span>
systemctl restart postgresql-9.6.service
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 查询连接数</span>
<span class="token keyword">select</span>  <span class="token operator">*</span> <span class="token keyword">from</span> pg_stat_activity<span class="token punctuation">;</span>
<span class="token comment">-- 杀掉空闲连接</span>
<span class="token keyword">SELECT</span> pg_terminate_backend<span class="token punctuation">(</span>pid<span class="token punctuation">)</span> <span class="token keyword">FROM</span> pg_stat_activity <span class="token keyword">WHERE</span> state<span class="token operator">=</span><span class="token string">'idle'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="ip连接权限"><a href="#ip连接权限" class="header-anchor">#</a> IP连接权限</h3> <p>data目录下的<strong>pg_hba.conf</strong>配置文件添加</p> <div class="language-php line-numbers-mode"><pre class="language-php"><code><span class="token comment"># 支持加密连接，默认仅现内网</span>
host    all  all     <span class="token number">0.0</span><span class="token number">.0</span><span class="token number">.0</span><span class="token operator">/</span><span class="token number">0</span>      md5
<span class="token comment"># 只有数据库用户就行，密码不验证</span>
host    all  all     <span class="token number">0.0</span><span class="token number">.0</span><span class="token number">.0</span><span class="token operator">/</span><span class="token number">0</span>      trust    
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="删除连接"><a href="#删除连接" class="header-anchor">#</a> 删除连接</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 查询当前连接，状态state字段为idle表示空闲连接</span>
<span class="token keyword">SELECT</span>
	<span class="token operator">*</span> 
<span class="token keyword">FROM</span>
	pg_stat_activity 
<span class="token keyword">WHERE</span>
	datname <span class="token operator">=</span> <span class="token string">'数据库名称'</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 删除连接</span>
<span class="token keyword">SELECT</span>
	pg_terminate_backend <span class="token punctuation">(</span>pid<span class="token punctuation">)</span> 
<span class="token keyword">FROM</span>
	pg_stat_activity 
<span class="token keyword">WHERE</span>
	datname <span class="token operator">=</span> <span class="token string">'数据库名称'</span> 
	<span class="token operator">AND</span> pid <span class="token operator">&lt;&gt;</span> pg_backend_pid <span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><h3 id="启动失败"><a href="#启动失败" class="header-anchor">#</a> 启动失败</h3> <blockquote><p>windows环境</p></blockquote> <ul><li><p>原因：</p> <ul><li>由于执行命令超时，导致事务锁住。</li></ul></li> <li><p>状况：</p> <ul><li>会在目录/data/pg_xlog/下会生成事务文件，删除文件。（删不掉提示权限问题，只能重启系统才可删）</li> <li>会在目录/data下生成pid后缀文件，删除文件</li></ul></li> <li><p>命令查询</p> <ul><li><p>重置事务日志</p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code>pg_resetxlog.exe -f <span class="token punctuation">..</span><span class="token punctuation">\</span>data
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div></li> <li><p>查看服务状态</p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code>pg_ctl.exe status -D <span class="token punctuation">..</span><span class="token punctuation">\</span>data

<span class="token comment"># 获取到pid，杀掉进程</span>
taskkill -pid <span class="token number">3242</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div></li></ul></li></ul> <h3 id="链接参数"><a href="#链接参数" class="header-anchor">#</a> 链接参数</h3> <div class="language-yaml line-numbers-mode"><pre class="language-yaml"><code><span class="token key atrule">spring</span><span class="token punctuation">:</span>
  <span class="token key atrule">datasource</span><span class="token punctuation">:</span>
    <span class="token key atrule">url</span><span class="token punctuation">:</span> jdbc<span class="token punctuation">:</span>postgresql<span class="token punctuation">:</span>//localhost<span class="token punctuation">:</span>5432/test_yh<span class="token punctuation">?</span><span class="token punctuation">?</span>currentSchema=sde
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><ul><li>currentSchema：指定schema模式</li></ul> <h2 id="sql"><a href="#sql" class="header-anchor">#</a> SQL</h2> <blockquote><p>数据库默认小写，所以sql的别名中也是忽略大小写的， 不要写驼峰形式</p></blockquote> <h3 id="查询系统配置"><a href="#查询系统配置" class="header-anchor">#</a> 查询系统配置</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 通过视图，查看当前连接信息</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> pg_stat_activity<span class="token punctuation">;</span>
<span class="token comment">-- 最大连接数</span>
<span class="token keyword">show</span> max_connections
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="查询数据库表"><a href="#查询数据库表" class="header-anchor">#</a> 查询数据库表</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
	tt<span class="token punctuation">.</span>tablename tableName<span class="token punctuation">,</span>	<span class="token comment">-- 表名</span>
	tt<span class="token punctuation">.</span>tableowner<span class="token punctuation">,</span>	<span class="token comment">-- 表所属用户</span>
	CAST <span class="token punctuation">(</span> obj_description <span class="token punctuation">(</span> cc<span class="token punctuation">.</span>oid  <span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">VARCHAR</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> tableComment	<span class="token comment">-- 表注释</span>
<span class="token keyword">FROM</span>
	pg_class cc
	<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> pg_tables tt <span class="token keyword">ON</span> tt<span class="token punctuation">.</span>tablename <span class="token operator">=</span> cc<span class="token punctuation">.</span>relname 
<span class="token keyword">WHERE</span>
	<span class="token number">1</span> <span class="token operator">=</span> <span class="token number">1</span> 
	<span class="token operator">AND</span> tt<span class="token punctuation">.</span>schemaname <span class="token operator">=</span> <span class="token string">'sde'</span> <span class="token comment">-- 模式</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>
	tt<span class="token punctuation">.</span>tablename
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br></div></div><h3 id="查询表字段"><a href="#查询表字段" class="header-anchor">#</a> 查询表字段</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
	pa<span class="token punctuation">.</span>attname <span class="token keyword">AS</span> columnName<span class="token punctuation">,</span> <span class="token comment">-- 字段名</span>
	pt<span class="token punctuation">.</span>typname <span class="token keyword">AS</span> dataType<span class="token punctuation">,</span>	<span class="token comment">-- 类型</span>
	pd<span class="token punctuation">.</span>description <span class="token keyword">AS</span> columnComment	<span class="token comment">-- 注释 </span>
<span class="token keyword">FROM</span>
	pg_class <span class="token keyword">AS</span> pc
	<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> pg_attribute <span class="token keyword">AS</span> pa <span class="token keyword">ON</span> pa<span class="token punctuation">.</span>attrelid <span class="token operator">=</span> pc<span class="token punctuation">.</span>oid
	<span class="token keyword">INNER</span> <span class="token keyword">JOIN</span> pg_type <span class="token keyword">AS</span> pt <span class="token keyword">ON</span> pa<span class="token punctuation">.</span>atttypid <span class="token operator">=</span> pt<span class="token punctuation">.</span>oid
	<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> pg_description <span class="token keyword">AS</span> pd <span class="token keyword">ON</span> pd<span class="token punctuation">.</span>objoid <span class="token operator">=</span> pa<span class="token punctuation">.</span>attrelid 
	<span class="token operator">AND</span> pd<span class="token punctuation">.</span>objsubid <span class="token operator">=</span> pa<span class="token punctuation">.</span>attnum 
<span class="token keyword">WHERE</span>
	pc<span class="token punctuation">.</span>relname <span class="token operator">=</span> <span class="token string">'my_user'</span> 
	<span class="token operator">AND</span> pa<span class="token punctuation">.</span>attnum <span class="token operator">&gt;</span> <span class="token number">0</span> 	<span class="token comment">-- 自定义表大于0，系统默认表小于0</span>
<span class="token keyword">ORDER</span> <span class="token keyword">BY</span>
	pc<span class="token punctuation">.</span>relname <span class="token keyword">DESC</span><span class="token punctuation">,</span>
	pa<span class="token punctuation">.</span>attnum <span class="token keyword">ASC</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br></div></div><h3 id="按时间统计"><a href="#按时间统计" class="header-anchor">#</a> 按时间统计</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span>
<span class="token comment">-- 将时间格式化，分组，按年就是YYYY，按填就是YYYY-MM-DD</span>
	to_char<span class="token punctuation">(</span> create_time<span class="token punctuation">,</span> <span class="token string">'YYYY-MM'</span> <span class="token punctuation">)</span> <span class="token keyword">AS</span> groupdate
<span class="token keyword">FROM</span>
	demo
<span class="token keyword">GROUP</span> <span class="token keyword">BY</span>
	groupdate
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><h3 id="建表语句"><a href="#建表语句" class="header-anchor">#</a> 建表语句</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 自增序列</span>
<span class="token keyword">CREATE</span> SEQUENCE <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo_id_seq&quot;</span> 
INCREMENT <span class="token number">1</span>
MINVALUE  <span class="token number">1</span>
MAXVALUE <span class="token number">9223372036854775807</span>
<span class="token keyword">START</span> <span class="token number">1</span>
CACHE <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> setval<span class="token punctuation">(</span><span class="token string">'&quot;public&quot;.&quot;demo_id_seq&quot;'</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token boolean">false</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> SEQUENCE <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo_id_seq&quot;</span> OWNER <span class="token keyword">TO</span> <span class="token string">&quot;postgres&quot;</span><span class="token punctuation">;</span>


<span class="token comment">-- 建表语句</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span> <span class="token punctuation">(</span>
	<span class="token string">&quot;id&quot;</span> int4 <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> nextval<span class="token punctuation">(</span> <span class="token string">'demo_id_seq'</span> :: regclass <span class="token punctuation">)</span><span class="token punctuation">,</span>
	<span class="token string">&quot;aa&quot;</span> <span class="token keyword">CHAR</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> <span class="token string">&quot;pg_catalog&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;default&quot;</span><span class="token punctuation">,</span>
	<span class="token string">&quot;bb&quot;</span> <span class="token keyword">VARCHAR</span> <span class="token punctuation">(</span><span class="token number">32</span><span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> <span class="token string">&quot;pg_catalog&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;default&quot;</span><span class="token punctuation">,</span>
	<span class="token string">&quot;cc&quot;</span> float8<span class="token punctuation">,</span>
	<span class="token string">&quot;dd&quot;</span> <span class="token keyword">TIMESTAMP</span> <span class="token punctuation">(</span><span class="token number">6</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
	<span class="token string">&quot;ee&quot;</span> <span class="token keyword">NUMERIC</span> <span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
	<span class="token keyword">CONSTRAINT</span> <span class="token string">&quot;demo_pk&quot;</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token string">&quot;id&quot;</span><span class="token punctuation">)</span> 
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span> OWNER <span class="token keyword">TO</span> <span class="token string">&quot;postgres&quot;</span><span class="token punctuation">;</span>
<span class="token keyword">COMMENT</span> <span class="token keyword">ON</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;id&quot;</span> <span class="token operator">IS</span> <span class="token string">'主键，依赖于自增序列'</span><span class="token punctuation">;</span>
<span class="token keyword">COMMENT</span> <span class="token keyword">ON</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;aa&quot;</span> <span class="token operator">IS</span> <span class="token string">'字符类型'</span><span class="token punctuation">;</span>
<span class="token keyword">COMMENT</span> <span class="token keyword">ON</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;bb&quot;</span> <span class="token operator">IS</span> <span class="token string">'字符串类型'</span><span class="token punctuation">;</span>
<span class="token keyword">COMMENT</span> <span class="token keyword">ON</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;cc&quot;</span> <span class="token operator">IS</span> <span class="token string">'浮点数类型，float8即double'</span><span class="token punctuation">;</span>
<span class="token keyword">COMMENT</span> <span class="token keyword">ON</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;dd&quot;</span> <span class="token operator">IS</span> <span class="token string">'时间类型，date类型不支持时分秒'</span><span class="token punctuation">;</span>
<span class="token keyword">COMMENT</span> <span class="token keyword">ON</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;ee&quot;</span> <span class="token operator">IS</span> <span class="token string">'数字类型'</span><span class="token punctuation">;</span>
<span class="token keyword">COMMENT</span> <span class="token keyword">ON</span> <span class="token keyword">TABLE</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span> <span class="token operator">IS</span> <span class="token string">'demo表，建表语句参考'</span><span class="token punctuation">;</span>


<span class="token comment">-- 新增字段</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span> <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;ss&quot;</span> <span class="token keyword">char</span><span class="token punctuation">(</span><span class="token number">8</span><span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> <span class="token string">&quot;pg_catalog&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;default&quot;</span><span class="token punctuation">;</span>
<span class="token keyword">COMMENT</span> <span class="token keyword">ON</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;ss&quot;</span> <span class="token operator">IS</span> <span class="token string">'新增字段'</span><span class="token punctuation">;</span>
<span class="token comment">-- 新增int</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span> <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;xqzmj&quot;</span> int8<span class="token punctuation">;</span>

<span class="token comment">-- 修改字段</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span> <span class="token keyword">ALTER</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;ss&quot;</span> <span class="token keyword">TYPE</span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span> <span class="token keyword">COLLATE</span> <span class="token string">&quot;pg_catalog&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;default&quot;</span><span class="token punctuation">;</span>
<span class="token keyword">COMMENT</span> <span class="token keyword">ON</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;ss&quot;</span> <span class="token operator">IS</span> <span class="token string">'修改字段'</span><span class="token punctuation">;</span>

<span class="token comment">-- 删除字段</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token string">&quot;public&quot;</span><span class="token punctuation">.</span><span class="token string">&quot;demo&quot;</span> <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> <span class="token string">&quot;ss&quot;</span><span class="token punctuation">;</span>

</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br></div></div><h3 id="函数用法"><a href="#函数用法" class="header-anchor">#</a> 函数用法</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 字符长度，字段qydm</span>
char_length<span class="token punctuation">(</span>qydm<span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h2 id="内部命令"><a href="#内部命令" class="header-anchor">#</a> 内部命令</h2> <h3 id="登录测试"><a href="#登录测试" class="header-anchor">#</a> 登录测试</h3> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 安装目录/bin目录下执行，如/usr/lib/postgresql/x.x/bin/</span>
psql -U postgres -h localhost -p <span class="token number">5432</span>
<span class="token comment"># 登录，查看版本</span>
<span class="token assign-left variable">postgres</span><span class="token operator">=</span><span class="token comment"># select version();</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="备份还原"><a href="#备份还原" class="header-anchor">#</a> 备份还原</h3> <ul><li><p>官方文档：备份用<a href="http://postgres.cn/docs/11/app-pgdump.html" target="_blank" rel="noopener noreferrer">pg_dump<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>，<a href="http://postgres.cn/docs/11/app-pg-dumpall.html" target="_blank" rel="noopener noreferrer">pg_dumpall<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a>，还原用<a href="http://postgres.cn/docs/11/app-psql.html" target="_blank" rel="noopener noreferrer">psql<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p></li> <li><p><a href="https://blog.csdn.net/ma286388309/article/details/112676354" target="_blank" rel="noopener noreferrer">参考博客<span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></p></li> <li><p><strong>Windows</strong>备份脚本参考如下</p> <div class="language-bat line-numbers-mode"><pre class="language-text"><code>:: 通用语句，关闭回显
@ECHO OFF
:: 设置本地延迟扩展，保证单行命令中多个命名延迟处理
@setlocal enableextensions
:: 通用语句，定位到脚本所在目录
@cd /d &quot;%~dp0&quot;
SET PGPATH=&quot;D:\YH\Program Files\PostgreSQL\9.2\bin\pg_dump&quot;
SET SVPATH=D:\YH\Program Files\PostgreSQL\9.2\backup\
:: 外部参数，第1是%1，依次类推
SET PRJDB=%1
SET DBUSR=postgres
SET DBROLE=postgres
:: 获取日期时间值
FOR /F &quot;TOKENS=1,2,3 DELIMS=/ &quot; %%i IN ('DATE /T') DO SET d=%%i-%%j-%%k
FOR /F &quot;TOKENS=1,2,3 DELIMS=: &quot; %%i IN ('TIME /T') DO SET t=%%i%%j%%k
SET DBDUMP=%SVPATH%%PRJDB%_%d%_%t%.dump
@ECHO OFF
:: 备份命令，参数说明参考文档
%PGPATH% -h localhost -p 5432 -U %DBUSR% --role %DBROLE% -v -f &quot;%DBDUMP%&quot; %PRJDB% 
echo Backup Taken Complete %DBDUMP%
:: 暂停命令，测试常用
pause

</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br></div></div><div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># 脚本运行 数据库名作为传参</span>
demo.bat mydb
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><blockquote><p>问题：</p> <p>若是运行后会要求提示输入密码口令（尤其是非本机数据库），需要手动配置<strong>pgpass</strong>！</p> <ul><li>Windows默认环境变量是%APPDATA%，比如<code>C:\Users\Administrator\AppData\Roaming\postgresql</code></li></ul> <p><code>pgpass.conf</code>文件内容如下</p> <div class="language-ini line-numbers-mode"><pre class="language-ini"><code><span class="token comment"># 地址、端口、数据库名、账户、密码</span>
localhost:5432:postgres:postgres:yanghan
<span class="token comment"># 例子</span>
localhost:5432:*:postgres:postgres
119.23.186.51:5432:*:postgres:postgres
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div></blockquote></li> <li><p>还原命令</p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code>psql -U postgres -d <span class="token builtin class-name">test</span> -f <span class="token string">&quot;D:\YH\xx.dump&quot;</span> 
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div></li></ul> <blockquote><p>window系统</p> <p>定时任务：搜索【任务计划程序】-【创建基本任务】配置信息，脚本参考如下</p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code><span class="token comment"># forfiles 系统自带命令</span>
forfiles /p %cd% /s /m <span class="token string">&quot;xx.*&quot;</span> /d -7 /c <span class="token string">&quot;cmd /c del /f @path&quot;</span>
<span class="token comment"># /p 指定目录，%cd%是变量</span>
<span class="token comment"># /s 递归搜索子目录</span>
<span class="token comment"># /m 所有文件类型，指定类型，直接写 *.jpg</span>
<span class="token comment"># /d 多少天前的文件，-7即七天，命名会根据文件最后修改时间判断</span>
<span class="token comment"># /c 运行指定命令，即双引号内的命令</span>
<span class="token comment"># ”cmd /c del /f @path“，其中/c表示最后关闭CMD窗口，强制删除文件</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div></blockquote> <blockquote><p>Linux系统</p> <p>安装目录<code>/usr/lib/postgresql/x.x/bin/</code></p> <p>定时任务，用<code>ctrontab</code></p></blockquote> <h2 id="安装"><a href="#安装" class="header-anchor">#</a> 安装</h2> <h3 id="docker-compose"><a href="#docker-compose" class="header-anchor">#</a> docker-compose</h3> <div class="language-yml line-numbers-mode"><pre class="language-yml"><code><span class="token key atrule">version</span><span class="token punctuation">:</span> <span class="token string">'3'</span>

<span class="token key atrule">services</span><span class="token punctuation">:</span>
  <span class="token key atrule">postgres9.2</span><span class="token punctuation">:</span>
    <span class="token key atrule">container_name</span><span class="token punctuation">:</span> postgres9.2
    <span class="token key atrule">image</span><span class="token punctuation">:</span> postgres<span class="token punctuation">:</span><span class="token number">9.2</span>
    <span class="token key atrule">environment</span><span class="token punctuation">:</span>
      <span class="token key atrule">POSTGRES_PASSWORD</span><span class="token punctuation">:</span> Abc123
    <span class="token key atrule">ports</span><span class="token punctuation">:</span>
    <span class="token comment"># 【54322可修改为自定义端口】</span>
      <span class="token punctuation">-</span> 54322<span class="token punctuation">:</span><span class="token number">5432</span>
    <span class="token key atrule">volumes</span><span class="token punctuation">:</span>
      <span class="token punctuation">-</span> 改为数据库存放目录<span class="token punctuation">:</span>/var/lib/postgresql
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br></div></div></div> <footer class="page-edit" style="display:none;"><!----> <div class="last-updated"><span class="prefix">Last Updated: </span> <span class="time">2022/1/18 下午2:43:34</span></div></footer> <!----> <!----></main> <!----></div></div></div></div><div class="global-ui"><div class="back-to-ceiling" style="right:1rem;bottom:6rem;width:2.5rem;height:2.5rem;border-radius:.25rem;line-height:2.5rem;display:none;" data-v-c6073ba8 data-v-c6073ba8><svg t="1574745035067" viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="5404" class="icon" data-v-c6073ba8><path d="M526.60727968 10.90185116a27.675 27.675 0 0 0-29.21455937 0c-131.36607665 82.28402758-218.69155461 228.01873535-218.69155402 394.07834331a462.20625001 462.20625001 0 0 0 5.36959153 69.94390903c1.00431239 6.55289093-0.34802892 13.13561351-3.76865779 18.80351572-32.63518765 54.11355614-51.75690182 118.55860487-51.7569018 187.94566865a371.06718723 371.06718723 0 0 0 11.50484808 91.98906777c6.53300375 25.50556257 41.68394495 28.14064038 52.69160883 4.22606766 17.37162448-37.73630017 42.14135425-72.50938081 72.80769204-103.21549295 2.18761121 3.04276886 4.15646224 6.24463696 6.40373557 9.22774369a1871.4375 1871.4375 0 0 0 140.04691725 5.34970492 1866.36093723 1866.36093723 0 0 0 140.04691723-5.34970492c2.24727335-2.98310674 4.21612437-6.18497483 6.3937923-9.2178004 30.66633723 30.70611158 55.4360664 65.4791928 72.80769147 103.21549355 11.00766384 23.91457269 46.15860503 21.27949489 52.69160879-4.22606768a371.15156223 371.15156223 0 0 0 11.514792-91.99901164c0-69.36717486-19.13165746-133.82216804-51.75690182-187.92578088-3.42062944-5.66790279-4.76302748-12.26056868-3.76865837-18.80351632a462.20625001 462.20625001 0 0 0 5.36959269-69.943909c-0.00994388-166.08943902-87.32547796-311.81420293-218.6915546-394.09823051zM605.93803103 357.87693858a93.93749974 93.93749974 0 1 1-187.89594924 6.1e-7 93.93749974 93.93749974 0 0 1 187.89594924-6.1e-7z" p-id="5405" data-v-c6073ba8></path><path d="M429.50777625 765.63860547C429.50777625 803.39355007 466.44236686 1000.39046097 512.00932183 1000.39046097c45.56695499 0 82.4922232-197.00623328 82.5015456-234.7518555 0-37.75494459-36.9345906-68.35043303-82.4922232-68.34111062-45.57627738-0.00932239-82.52019037 30.59548842-82.51086798 68.34111062z" p-id="5406" data-v-c6073ba8></path></svg></div></div></div>
    <script src="/blog/assets/js/app.0c46c7d8.js" defer></script><script src="/blog/assets/js/3.5679fe00.js" defer></script><script src="/blog/assets/js/1.8cdd2163.js" defer></script><script src="/blog/assets/js/18.0a7a10ef.js" defer></script>
  </body>
</html>
